package Day1.ZuoYe;

import java.sql.*;
import java.util.Scanner;

//1：设计一个DbUtil类，处理相关数据库的操作，
//包括获得链接，关闭相关资源的方法
//最好用Static方法
public class DbUtil {
    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/wanghaibo?useSSL=false&characterEncoding=UTF-8&serverTimezone=GMT%2b8";
    private static final String SQLUSER_NAME = "root";
    private static final String SQLUSER_PASSWORD = "123456";

    //建立连接
    public Connection connect() {
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            System.exit(1);
            System.out.println("无法找到驱动类！");
            e.printStackTrace();
        }
        Connection con = null;
        try {
            con = DriverManager.getConnection(JDBC_URL, SQLUSER_NAME, SQLUSER_PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public Statement statement(Connection con) {
        Statement sta = null;
        try {
            sta = con.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sta;
    }

    // 关闭Connection连接
    public static void close(Connection con, Statement sta) {
        try {
            if (sta != null)
                sta.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (con != null)
                con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 2: 统计学生总数。并输出。
    public int allStudent(Statement sta, String sql) {
        ResultSet res = null;
        int stu = 0;
        try {
            res = sta.executeQuery(sql);
            if (res.next())
                stu = res.getInt("count(stuno)");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (res != null)
                    res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return stu;
    }

    //3:增加一个课程，相关信息输入
    //4:修改一个课程信息，通过id（编号）
    public int addLesson(Statement sta, String sql) {
        int i = 0;
        try {
            i = sta.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    //加学生
    public int add(PreparedStatement ps) {
        int i = 0;
        try {
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        DbUtil db = new DbUtil();
        Connection con = db.connect();
        if (con != null)
            System.out.println("连接成功");
        else
            System.out.println("连接失败");
        Statement sta = db.statement(con);
        System.out.println("请输入查询总学生数的SQL语句：");
        String s = input.nextLine();
        int i = db.allStudent(sta, s);
        System.out.println("学生总数是：" + i + "人");

        //3:增加一个课程，相关信息输入
        System.out.println("请输入课程名字：");
        String sbname = input.next();
        System.out.println("请输入课程时长：");
        int hour = input.nextInt();
        System.out.println("请输入年纪编号：");
        int gradeid = input.nextInt();
        int a = db.addLesson(sta,
                "insert into s_subject(subjectname,classhour,gradeid) values("
                        + "'" + sbname + "'" + "," + "'" + hour + "'" + "," + "'" + gradeid + "'" + ")");
        if (a != 0)
            System.out.println("增加成功");
        else
            System.out.println("增加失败");

        //4:修改一个课程信息，通过id（编号）
        System.out.println("请输入需要更改课程的ID：");
        int id = input.nextInt();
        System.out.println("请选择您需要更改的项：subjectname，classhour，gradeid");
        String chooce = input.next();
        System.out.println("请输入您更改的内容：");
        String s1 = input.next();
        String url = "update s_subject set " + chooce + "='" + s1 + "' where id=" + id;
        if (db.addLesson(sta, url) != 0)
            System.out.println("修改成功");
        else
            System.out.println("修改失败");

        //5:在控制台输入一个学生的全部信息，保存到数据库，注意日期的处理(PreparedStatement)
        System.out.println("请输入新同学的姓名：");
        String name = input.next();
        System.out.println("请输入新同学的性别：");
        String sex = input.next();
        System.out.println("请输入新同学的电话：");
        String telephone = input.next();
        System.out.println("请输入新同学的QQ：");
        String qq = input.next();
        System.out.println("请输入新同学的微信：");
        String wechat = input.next();
        System.out.println("请输入新同学的年级号：");
        int grade = input.nextInt();
        System.out.println("请输入新同学的入学时间：");
        String enterdate = input.next();
        System.out.println("请输入新同学的籍贯：");
        String fromcity = input.next();
        System.out.println("请输入新同学的生日：");
        String birthday = input.next();
        System.out.println("请输入新同学的住址：");
        String adress = input.next();
        System.out.println("请输入新同学的密码：");
        String password = input.next();
        System.out.println("请输入新同学的身份证号码：");
        String idcard = input.next();
        String sql = "insert into s_student(sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, sex);
            ps.setString(3, telephone);
            ps.setString(4, qq);
            ps.setString(5, wechat);
            ps.setInt(6, grade);
            ps.setDate(7, Date.valueOf(enterdate));
            ps.setString(8, fromcity);
            ps.setDate(9, Date.valueOf(birthday));
            ps.setString(10, adress);
            ps.setString(11, password);
            ps.setString(12, idcard);
            if (db.add(ps) != 0)
                System.out.println("增加成功");
            else
                System.out.println("增加失败");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close(con, sta);
    }
}
